10.1: SQLite Database
Contents:
- Using SQLite databases with Android
- Cursor
- ContentValues
- Implementing an SQLite database
- Database operations
- Instantiate Open Helper
- Working with the database
- Transactions
- Backing up databases
- Shipping a database with your APK
- Related practical
- Learn more
This chapter discusses the Android framework's SQLiteDatabase and SQLiteOpenHelper classes. It is not an introduction to SQLite or SQL databases.The chapter assumes that you are familiar with SQL databases in general, and basic SQL query building. Check out the SQL Primer chapter if you need a refresher.
Of the many storage options discussed, using a SQLite database is one of the most versatile, and straightforward to implement.
- An SQLite database is a good storage solution when you have structured data that you need to store persistently and access, search, and change frequently.
- You can use the database as the primary storage for user or app data, or you can use it to cache and make available data fetched from the cloud.
- If you can represent your data as rows and columns, consider a SQLite database.
- Content providers, which will be introduced in a later chapter, work excellently with SQLite databases.
When you use an SQLite database, represented as an SQLiteDatabase object, all interactions with the database are through an instance of the SQLiteOpenHelper class which executes your requests and manages your database for you. Your app should only interact with the SQLiteOpenHelper, which will be described below.
There are two data types associated with using SQLite databases in particular, Cursor and ContentValues.
Cursor
The SQLiteDatabase always presents the results as a Cursor in a table format that resembles that of a SQL database.
You can think of the data as an array of rows. A cursor is a pointer into one row of that structured data. The Cursor class provides methods for moving the cursor through the data structure, and methods to get the data from the fields in each row.
The Cursor class has a number of subclasses that implement cursors for specific types of data.
- SQLiteCursor exposes results from a query on a SQLiteDatabase. SQLiteCursor is not internally synchronized, so code using a SQLiteCursor from multiple threads should perform its own synchronization when using the SQLiteCursor.
- MatrixCursor is an all-rounder, a mutable cursor implementation backed by an array of objects that automatically expands internal capacity as needed.
Some common operations on cursor are:
- getCount() returns the number of rows in the cursor.
- getColumnNames() returns a string array holding the names of all of the columns in the result set in the order in which they were listed in the result.
- getPosition() returns the current position of the cursor in the row set.
- Getters are available for specific data types, such as getString(int column) and getInt(int column).
- Operations such as moveToFirst() and moveToNext() move the cursor.
- close() releases all resources and makes the cursor completely invalid. Remember to call close to free resources!
Processing cursors
When a method call returns a cursor, you iterate over the result, extract the data, do something with the data, and finally, you must close the cursor to release the memory. Failing to do so can crash your app when it runs out of memory.
The cursor starts before the first result row, so on the first iteration you move the cursor to the first result if it exists. If the cursor is empty, or the last row has already been processed, then the loop exits. Don't forget to close the cursor once you're done with it. (This cannot be repeated too often.)
// Perform a query and store the result in a Cursor
Cursor cursor = db.rawQuery(...);
try {
while (cursor.moveToNext()) {
// Do something with the data
}
} finally {
cursor.close();
}
When you use a SQL database, you can implement your SQLiteOpenHelper class to return the cursor to the calling activity or adapter, or you can convert the data to a format that is more suitable for the adapter. The advantage of the latter is that managing the cursor (and closing it) is handled by the open helper, and your user interface is independent of what happens at the backend. See the SQLite Database practical for an implementation example.
ContentValues
Similar to how extras stores data, an instance of ContentValues stores data as key-value pairs, where the key is the name of the column and the value is the value for the cell. One instance of ContentValues represents one row of a table.
The insert() method for the database requires that the values to fill a row are passed as an instance of ContentValues.
ContentValues values = new ContentValues();
// Insert one row. Use a loop to insert multiple rows.
values.put(KEY_WORD, "Android");
values.put(KEY_DEFINITION, "Mobile operating system.");
db.insert(WORD_LIST_TABLE, null, values);
Implementing an SQLite database
To implement a database for your Android app, you need to do the following.
- (Recommended) Create a data model.
Subclass SQLiteOpenHelper
Use constants for table names and database creation query
Implement onCreate to create the SQLiteDatabase with tables for your data
Implement onUpgrade()
Implement optional methods
- Implement the query(), insert(), delete(), update(), count() methods in SQLiteOpenHelper.
- In your MainActivity, create an instance of SQLiteOpenHelper.
- Call methods of SQLiteOpenHelper to work with your database.
Caveats:
- When you implement the methods, always put database operations into try/catch blocks.
- The sample apps do not validate the user data. When you write an app for publication, always make sure user data is what you expect to avoid the injection of bad data or execution of malicious SQL commands into your database.
Data model
It is a good practice to create a class that represents your data with getters and setters.
For an SQLite database, an instance of this class could represent one record, and for a simple database, one row in a table.
public class WordItem {
private int mId;
private String mWord;
private String mDefinition;
// Getters and setters and more
}
Subclass SQLiteOpenHelper
Any open helper you create must extend SQLiteOpenHelper.
public class WordListOpenHelper extends SQLiteOpenHelper {
public WordListOpenHelper(Context context) {
super(context, DATABASE_NAME, null, DATABASE_VERSION);
Log.d(TAG, "Construct WordListOpenHelper");
}
}
Define constants for table names
While not required, it is customary to declare your table, column, and row names as constants. This makes your code a lot more readable, makes it easier to change names, and your queries will end up looking a lot more like SQL. You can do this in the open helper class, or in a separate public class; you will learn more about this in the chapter about content providers.
private static final int DATABASE_VERSION = 1;
// has to be 1 first time or app will crash
private static final String WORD_LIST_TABLE = "word_entries";
private static final String DATABASE_NAME = "wordlist";
// Column names...
public static final String KEY_ID = "_id";
public static final String KEY_WORD = "word";
// ... and a string array of columns.
private static final String[] COLUMNS = {KEY_ID, KEY_WORD};
Define query for creating database
You need a query that creates a table to create a database. This is also customarily defined as a string constant. This basic example creates one table with a column for an auto-incrementing id and a column to hold words.
private static final String WORD_LIST_TABLE_CREATE =
"CREATE TABLE " + WORD_LIST_TABLE + " (" +
KEY_ID + " INTEGER PRIMARY KEY, " +
// will auto-increment if no value passed
KEY_WORD + " TEXT );";
Implement onCreate() and create the database
The onCreate method is only called if there is no database. Create your tables in the method, and optionally add initial data.
@Override
public void onCreate(SQLiteDatabase db) { // Creates new database
db.execSQL(WORD_LIST_TABLE_CREATE); // Create the tables
fillDatabaseWithData(db); // Add initial data
// Cannot initialize mWritableDB and mReadableDB here, because
// this creates an infinite loop of on Create()
// being repeatedly called.
}
Implement onUpgrade()
This is a required method.
If your database acts only as a cache for data that is also stored online, you can drop the the tables and recreate them after the upgrade is complete.
@Override
public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
// SAVE USER DATA FIRST!!!
Log.w(WordListOpenHelper.class.getName(),
"Upgrading database from version " + oldVersion + " to "
+ newVersion + ", which will destroy all old data");
db.execSQL("DROP TABLE IF EXISTS " + WORD_LIST_TABLE);
onCreate(db);
}
Optional methods
The open helper class provides additional methods that you can override as needed.
- onDowngrade()—The default implementation rejects downgrades.
- onConfigure()—called before onCreate. Use this only to call methods that configure the parameters of the database connection.
- onOpen()—Any work other than configuration that needs to be done after the database is opened.
Database operations
While you can call your methods in the open helper anything you want and have them return anything you choose to the calling activity, it is a good idea to go with the standardized query(), insert(), delete(), update(), count() methods that match the API of the database and content providers. Using this format will make it easier to add a content provider or loader in the future, and it makes it easier for other people to understand your code.
The following diagram shows how the different API's should be designed for consistency and clarity.
query()
The query method that you implement in your open helper class can take and return any data type that your user interface needs.
Since the open helper provides convenience methods for inserting, deleting, and updating rows, your query method does not need to be generic and support these operations.
In general, your query method should only allow queries that are needed by your app and not be general purpose.
The database provides two methods for sending queries: SQLiteDatabase.rawQuery() and SQLiteDatabase.query(), with several options for the arguments.
SQLiteDatabase.rawQuery()
The open helper query method can construct an SQL query and send it as a rawQuery to the database which returns a cursor. If your data is supplied by your app, and under your full control, you can use rawQuery().
rawQuery(String sql, String[] selectionArgs)
- The first parameter to db.rawquery() is an SQLite query string.
- The second parameter contains the arguments.
cursor = mReadableDB.rawQuery(queryString, selectionArgs);
SQLiteDatabase.query()
If you are processing user-supplied data, even after validation, it is more secure to construct a query and use a version of the SQLiteDatabase.query() method for the database. The arguments are what you'd expect in SQL and are documented in the SQLiteDatabase documentation.
Cursor query (boolean distinct, String table, String[] columns, String selection,
String[] selectionArgs, String groupBy, String having,
String orderBy,String limit)
Here is a basic example:
String[] columns = new String[]{KEY_WORD};
String where = KEY_WORD + " LIKE ?";
searchString = "%" + searchString + "%";
String[] whereArgs = new String[]{searchString};
cursor = mReadableDB.query(WORD_LIST_TABLE, columns, where, whereArgs, null, null, null);
Example of complete open helper query()
public WordItem query(int position) {
String query = "SELECT * FROM " + WORD_LIST_TABLE +
" ORDER BY " + KEY_WORD + " ASC " +
"LIMIT " + position + ",1";
Cursor cursor = null;
WordItem entry = new WordItem();
try {
if (mReadableDB == null) {mReadableDB = getReadableDatabase();}
cursor = mReadableDB.rawQuery(query, null);
cursor.moveToFirst();
entry.setId(cursor.getInt(cursor.getColumnIndex(KEY_ID)));
entry.setWord(cursor.getString(cursor.getColumnIndex(KEY_WORD)));
} catch (Exception e) {
Log.d(TAG, "EXCEPTION! " + e);
} finally {
// Must close cursor and db now that we are done with it.
cursor.close();
return entry;
}
}
insert()
The open helper's insert() method calls SQLiteDatabase.insert(), which is a SQLiteDatabase convenience method to insert a row into the database. (It's a convenience method, because you do not have to write the SQL query yourself.)
Format
long insert(String table, String nullColumnHack, ContentValues values)
- The first argument is the table name.
- The second argument is a
String nullColumnHack
. It's a workaround that allows you to insert empty rows. See the documentation for insert(). Use null. - The third argument must be a ContentValues container with values to fill the row. This sample only has one column; for tables with multiple columns, you add the values for each column to this container.
- The database method returns the id of the newly inserted item, and you should pass that on to the application.
Example
newId = mWritableDB.insert(WORD_LIST_TABLE, null, values);
delete()
The open helper delete method calls the databases delete() method, which is a convenience method so that you do not have to write the full SQL query.
Format
int delete (String table, String whereClause, String[] whereArgs)
- The first argument is the table name.
- The second argument is a WHERE clause.
- The third argument are the arguments to the WHERE clause.
You can delete using any criteria, and the method returns the number of items that were actually deleted, which the open helper should return also.
Example
deleted = mWritableDB.delete(WORD_LIST_TABLE,
KEY_ID + " =? ", new String[]{String.valueOf(id)});
update()
The open helper update method calls the database's update() method, which is a convenience method so that you do not have to write the full SQL query. The arguments are familiar from previous methods, and the onUpdate returns the number of rows updated.
Format
int update(String table, ContentValues values,
String whereClause, String[] whereArgs)
- The first argument is the table name.
- The second argument must be a ContentValues with new values for the row.
- The third argument is a WHERE clause.
- The fourth argument are the arguments to the WHERE clause.
Example
ContentValues values = new ContentValues();
values.put(KEY_WORD, word);
mNumberOfRowsUpdated = mWritableDB.update(WORD_LIST_TABLE,
values, // new values to insert
KEY_ID + " = ?",
new String[]{String.valueOf(id)});
count()
The count() method returns the number of entries in the database. If you are using a RecyclerView.Adapter, it has to implement getItemCount(), which needs to get the number of rows from the open helper which needs to get it from the database.
In adapter
@Override
public int getItemCount() {
return (int) mDB.count();
}
In the open helper
public long count(){
if (mReadableDB == null) {mReadableDB = getReadableDatabase();}
return DatabaseUtils.queryNumEntries(mReadableDB, WORD_LIST_TABLE);
}
queryNumEntries()) is a method in the public DatabaseUtils class, which provides many convenience methods for working with cursors, databases, and also content providers.
Instantiate Open Helper
To get a handle to the database, In MainActivity, in onCreate, call:
mDB = new WordListOpenHelper(this);
Working with the database
It is a common pattern to combine a SQLiteDatabase backend with a RecyclerView to display the data.
For example:
- Pressing the FAB could start an activity that gets input from the user and stores it into the database as a new or updated item.
- Swiping an item might delete it after the user confirms deletion.
Transactions
Use transactions
- when performing multiple operations that all need to complete to keep database consistent, for example, updating pricing of related items for a sale event.
- to batch multiple independent operations to improve performance, such as mass inserts.
Transactions can be nested, and the SQLiteDatabase class provides additional methods to manage nested transactions. See SQLiteDatabase references documentation.
Transaction idiom
db.beginTransaction();
try {
...
db.setTransactionSuccessful();
} finally {
db.endTransaction();
}
Backing up databases
It is a good idea to back up your app's database.
You can do so using the Cloud Backup options discussed in the Storage Options chapter.
Shipping a database with your app
Sometimes you may want to include a populated database with your app. There are several ways in which to do that, and there are trade-offs for each.
- Include the SQL commands with the application and have it create the database and insert the data on first use. This is basically what you will do in the practical for data storage. If the amount of data you want put in the database is small, just an example so that the user gets to see something, you can use this method.
- Ship the data with the APK as a resource, and build the database when the user opens the app for the first time. This is similar to the first method, but instead of defining your data in your code, you put it in a resource, for example, in CSV format. You can then read the data with an input stream and add it to the database.
- Build and pre-populate the SQLite database and include it in the APK. With this method you write an app that creates and populates a database. You can do this on the emulator. You then copy the file in which your database is actually stored ( "/data/data/YOUR_PACKAGE/databases/" directory) and include it as an asset with your app. When the app is started for the first time, you copy the database file back into the "/data/data/YOUR_PACKAGE/databases/" directory.
The SQLiteAssetHelper class, which you can download from Github, extends SQLiteOpenHelper to help you do this. And this Stackoverflow post discusses this topic in more detail.
Note that for a larger database, populating the database should be done in the background, and your app should not crash if there is no database yet, or the database is empty.
Related practical
The related practical documentation is in Android Developer Fundamentals: Practicals.
Learn more
- Storage Options
- Saving Data in SQL Databases
- SQLiteDatabase class
- ContentValues class
- SQLiteOpenHelper class
- Cursor class
- SQLiteAssetHelper class from Github